﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using System.Data;

namespace BOM.dao
{
    class DbService
    {
        private static String  strConnection="Provider=Microsoft.Jet.OleDb.4.0;Data Source=D:/bom.mdb";

        public static OleDbConnection conn = null;

        //检测OleConnection是否为空 利用连接字符串 实例化对象
        public static  OleDbConnection getConnection(){
            if(conn == null)
               conn=new OleDbConnection(strConnection); //利用连接字符串 实例化对象
            return conn;
        }

        //传入SQL语句 返回受影响的行数
        public static int executeSQL(string cmdText)//执行SQL语句,1个参数,为所执行的语句,返回整数值为判断所影响的行数
        {
            int iValue = -1;
            //实例化OleDbConnection对象 也就是连接access数据库
            OleDbConnection oleConnecion = getConnection(); 
            oleConnecion.Open();
            OleDbTransaction oleTransaction = oleConnecion.BeginTransaction();
            try
            {
                OleDbCommand oleCommand = new OleDbCommand();
                oleCommand.Connection = oleConnecion;
                oleCommand.CommandText = cmdText;
                oleCommand.Transaction = oleTransaction;
                iValue = oleCommand.ExecuteNonQuery();
                oleTransaction.Commit();
            }
            catch (System.Data.OleDb.OleDbException ex)
            {
                oleTransaction.Rollback();
                throw ex;
            }
            catch (System.Exception ex2)
            {
                throw ex2;
            }
            finally
            {
                oleConnecion.Close();
            }
            return iValue;
        }

        //传入SQL语句 返回一个表
        public static DataTable getSQLTable(string selectSQL, string tableName)//获得一个表,2个参数,一个SQL语句,一个表名
        {
            DataTable table = new DataTable(tableName); //为这个新实例指定表名
            OleDbConnection oleConnection = getConnection();
            try
            {
                //进入数据库查询 把查询结果添加到table中
                OleDbDataAdapter oleDataAdapter = new OleDbDataAdapter(selectSQL, strConnection);
                oleDataAdapter.Fill(table);
            }
            catch (System.Data.OleDb.OleDbException ex)
            {
                throw ex;
            }
            catch (System.Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (oleConnection.State != ConnectionState.Closed)
                {
                    oleConnection.Close();
                }
            }
            return table;
        }

    }  //end class


}
